/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package jdh;

import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.table.AbstractTableModel;
import java.sql.*;

/**
 *
 * @author tale
 */
public class DataModel extends AbstractTableModel{
    String table;
    Statement st, ust = null;
    ResultSet rs;
    ResultSetMetaData md;
    Backend backend;
    int column = 0;
    int row = 0;

    public DataModel(Backend backend, String table) throws Exception{
        this.backend = backend;
        this.table = table;
        this.st = backend.getConnection().createStatement();
        if (refresh() == false)
            throw new Exception(" DataModel() initial model failed.");
    }

    public boolean refresh(){
        boolean value =false;
        String query = "SELECT * FROM " + table;

        row = 0;
        column = 0;
        System.out.println("initial by: " + table);

        try {
            rs = st.executeQuery(query);
            md = rs.getMetaData();
            column = md.getColumnCount();
            if (rs.last()){
                row = rs.getRow();
                rs.beforeFirst();
            }
            value = true;
        }catch(SQLException ex) {
            System.out.println(ex.toString());
        }catch(NullPointerException ex){
            System.out.println(ex.toString());
        }finally{
            System.out.println("Column = " + column + "\tRow = " + row);
        }
        return value;
    }

    public int getRowCount() {
        return row;
    }

    public int getColumnCount() {
        return column;
    }

    @Override
    public String getColumnName(int columnIndex) {
        try {
            return md.getColumnName(columnIndex + 1);
        } catch (SQLException ex) {
            System.out.println(ex.toString());
            return "Column " + columnIndex;
        }
    }

    @Override
    public boolean isCellEditable(int rowIndex, int columnIndex) {
        return true;
    }

    public Object getValueAt(int rowIndex, int columnIndex) {
        try{
            rs.beforeFirst();
            while (rs.next()){
                if (rs.getRow() == rowIndex + 1)
                        return rs.getString(columnIndex + 1);

            }
        }catch(SQLException ex){
            System.out.println(ex.toString());
        }
        return null;
    }

    @Override
    public void setValueAt(Object aValue, int rowIndex, int columnIndex) {
        String label = getColumnName(columnIndex);
        String update = "UPDATE " + table
                + " SET " + label + " = '" + aValue
                + "' WHERE " + label + " = '" + getValueAt(rowIndex, columnIndex) + "'";
        System.out.println("SQL:" + update);
        try{
            if (ust == null)
                ust = backend.getConnection().createStatement();
            ust.executeUpdate(update);
        } catch (SQLException ex) {
            System.out.println(ex.toString());
        }finally{
            this.refresh();
        }
    }


    /*
CREATE TABLE PLANS(ID INT PRIMARY KEY AUTO_INCREMENT, NAME CHAR(30), DESCRPTION CHAR(255));
INSERT INTO PLANS VALUES(1, "PLAN 007", "A PLAN ABOUT THE 007");

CREATE TABLE TEACHERS(ID INT PRIMARY KEY, NAME CHAR(30));
INSERT INTO TEACHERS VALUES(012007001, "陈洁");
INSERT INTO TEACHERS VALUES(012007002, "陈力");

CREATE TABLE STUDENTS(ID INT PRIMARY KEY, NAME CHAR(30));
INSERT INTO STUDENTS VALUES(012007002, "阿飞");
INSERT INTO STUDENTS VALUES(012007003, "铁南");

 */

}
